﻿Imports NVelocity
Imports NVelocity.App
Imports NVelocity.Context
Partial Class WebForm_ManageEcolife_ExportEcolifeReport
    Inherits System.Web.UI.UserControl

    Protected Modeler As New MyModeler(Me)
    Protected Viewer As New MyViewer(Me)

    Public Class MyViewer
        Inherits MyMVC.ViewerBase

        Dim data As WebForm_ManageEcolife_ExportEcolifeReport

        Public Sub New(ByVal dataInternal As WebForm_ManageEcolife_ExportEcolifeReport)
            MyBase.New(dataInternal)
            Me.data = dataInternal
        End Sub

#Region "PL"
        Public Sub ReInitData()

            ScriptManager.GetCurrent(data.Page).RegisterPostBackControl(data.btnPrint)
        End Sub

#End Region
    End Class

    Public Class MyModeler
        Inherits MyMVC.ModelerBase

        Dim data As WebForm_ManageEcolife_ExportEcolifeReport

        Public Sub New(ByVal dataInternal As WebForm_ManageEcolife_ExportEcolifeReport)
            MyBase.New(dataInternal)
            Me.data = dataInternal
        End Sub
#Region "BLL-Method"

        Public Sub DoPrint()

            'Print
            Dim fileName As String = "EcolifeReport.xls"
            data.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName)

            Dim velocity As New VelocityEngine
            Dim props As New Commons.Collections.ExtendedProperties

            'fix bug
            props.AddProperty("file.resource.loader.path", data.Server.MapPath("~\App_Data"))
            props.AddProperty("input.encoding", "utf-8")
            props.AddProperty("output.encoding", "utf-8")
            velocity.Init(props)

            Dim template As Template = velocity.GetTemplate("ECOLIFE日誌匯入報表.xml")

            Dim context As New VelocityContext
            context.Put("User", clsAuth.GetUserID())
            context.Put("DownloadDate", Today.Year.ToString() & "-" & Today.Month.ToString() & "-" & Today.Day.ToString() & "T" & Now.Hour.ToString() & ":" & Now.Minute.ToString() & ":" & Now.Second.ToString() & "Z")
            context.Put("IP", MyObj.getIP())
            context.Put("Year", data.AnnualControl1.AnaualText)
            context.Put("Month", data.MonthlyControl1.SelectedValue)
            If data.ddlTownID.SelectedValue = "" Then
                context.Put("Location", "新北市環保局")
            Else
                context.Put("Location", data.ddlTownID.SelectedItem.Text)
            End If


            context.Put("NumberFormat1", "#,##0_ ")
            Dim EcolifeList As List(Of EcolifeDataRow) = Me.GetEcolifeData()
            context.Put("EcolifeRowCount", 3 + EcolifeList.Count)
            context.Put("EcolifeList", EcolifeList)
            context.Put("EcolifeCount", EcolifeList.Count)

            '結束產出

            Dim writer As New IO.StringWriter
            template.Merge(context, writer)

            data.Response.Write(writer.GetStringBuilder().ToString())
            data.Response.End()

        End Sub


        Protected Function GetEcolifeData() As List(Of EcolifeDataRow)
            Dim dt As Data.DataTable
            Dim sql As New MyStringBuilder
            Dim dat As New clsData

            Dim l As New List(Of EcolifeDataRow)
     
     
            dt = GetMyDT()
            For Each dr As Data.DataRow In dt.Rows

                Dim v As New EcolifeDataRow
      
                v.LocationB = dr("TownName").ToString()
                v.TownID = dr("RegionID").ToString()
                l.Add(v)

            Next


            sql.Length = 0
            sql.AppendFormat("SELECT  eco.LocationB AS LocationB, ")
            sql.AppendFormat("eco.LocationC AS LocationC,  ")
            sql.AppendFormat("eco.LogType AS LogType, ")
            sql.AppendFormat("eco.PublishDate AS Importdate, ")
            sql.AppendFormat("ev.LogType as VLogType, ")
            sql.AppendFormat("left(c.RegionID,3) as TownID ")
            sql.AppendFormat(",c.RegionID as RegionID ")
            sql.AppendFormat(",isnull(count(*), 0) as Count1 ")
            sql.AppendFormat("FROM EcolifeDataView eco ")
            sql.AppendFormat("INNER JOIN ")
            sql.AppendFormat("(Select r2.RegionName+r1.RegionName as TownName ,r1.RegionID from Region r1 ")
            sql.AppendFormat("INNER JOIN Region r2 on r1.Parent=r2.RegionID ")
            sql.AppendFormat("WHERE r1.RegionType='V' ) c on (eco.LocationB+ eco.LocationC)=c.TownName ")
            sql.AppendFormat("LEFT JOIN  ( ")
            sql.AppendFormat("SELECT ei.LocationB, ei.LogType, ei.PublishDate, ")
            sql.AppendFormat("ei.EcolifeID FROM EcolifeDataView ei ")
            sql.AppendFormat("INNER JOIN VolunteerDistinctView vi on ei.EcolifeID=vi.EcoLifeID ")
            sql.AppendFormat(") ev on eco.EcoLifeID=ev.EcoLifeID ")
            sql.AppendFormat("WHERE 1=1 ")
            sql.AppendFormat("AND Year(eco.PublishDate)='{0}' ", Int32.Parse(data.AnnualControl1.AnaualText) + 1911)
            sql.AppendFormat("AND Month(eco.PublishDate)='{0}' ", Int32.Parse(data.MonthlyControl1.SelectedValue))
            sql.AppendFormat("group by  eco.LocationB, eco.LocationC, eco.LogType, eco.PublishDate,ev.LogType,c.RegionID ")
            dt = dat.GetData(sql)

            Dim AddSql As String = ""
            For Each v As EcolifeDataRow In l
                If data.ddlTownID.SelectedValue = "" Then
                    '代表要取總和
                    AddSql = String.Format("and LocationB = '{0}' ", v.LocationB)
                Else

                    AddSql = String.Format("and RegionID = '{0}'  ", v.TownID)
                End If

                dt.DefaultView.RowFilter = String.Format("LogType = '清理日誌' " & AddSql)
                If dt.DefaultView.Count > 0 Then
                    v.LogTypeA = GetCount(dt, "Count1")
                Else
                    v.LogTypeA = 0
                End If

                dt.DefaultView.RowFilter = String.Format("LogType = '巡檢日誌' " & AddSql)
                If dt.DefaultView.Count > 0 Then
                    v.LogTypeB = GetCount(dt, "Count1")
                Else
                    v.LogTypeB = 0
                End If

                dt.DefaultView.RowFilter = String.Format("LogType = '通報日誌' " & AddSql)
                If dt.DefaultView.Count > 0 Then
                    v.LogTypeC = GetCount(dt, "Count1")
                Else
                    v.LogTypeC = 0
                End If

                dt.DefaultView.RowFilter = String.Format("VLogType = '清理日誌' " & AddSql)
                If dt.DefaultView.Count > 0 Then
                    v.VLogTypeA = GetCount(dt, "Count1")
                Else
                    v.VLogTypeA = 0
                End If

                dt.DefaultView.RowFilter = String.Format("VLogType = '巡檢日誌' " & AddSql)
                If dt.DefaultView.Count  > 0 Then
                    v.VLogTypeB = GetCount(dt, "Count1")
                Else
                    v.VLogTypeB = 0
                End If

                dt.DefaultView.RowFilter = String.Format("VLogType = '通報日誌' " & AddSql)
                If dt.DefaultView.Count > 0 Then
                    v.VLogTypeC = GetCount(dt, "Count1")
                Else
                    v.VLogTypeC = 0
                End If

            Next



            Return l
        End Function

        Public Class EcolifeDataRow

            Public TownIDField As String
            Public Property TownID() As String
                Get
                    Return Me.TownIDField
                End Get
                Set(ByVal value As String)
                    Me.TownIDField = value
                End Set
            End Property
            Public LocationBField As String
            Public Property LocationB() As String
                Get
                    Return Me.LocationBField
                End Get
                Set(ByVal value As String)
                    Me.LocationBField = value
                End Set
            End Property
            Public LogTypeAField As String
            Public Property LogTypeA() As String
                Get
                    Return Me.LogTypeAField
                End Get
                Set(ByVal value As String)
                    Me.LogTypeAField = value
                End Set
            End Property

            Public LogTypeBField As String
            Public Property LogTypeB() As String
                Get
                    Return Me.LogTypeBField
                End Get
                Set(ByVal value As String)
                    Me.LogTypeBField = value
                End Set
            End Property

            Public LogTypeCField As String
            Public Property LogTypeC() As String
                Get
                    Return Me.LogTypeCField
                End Get
                Set(ByVal value As String)
                    Me.LogTypeCField = value
                End Set
            End Property
            Public VLogTypeAField As String
            Public Property VLogTypeA() As String
                Get
                    Return Me.VLogTypeAField
                End Get
                Set(ByVal value As String)
                    Me.VLogTypeAField = value
                End Set
            End Property

            Public VLogTypeBField As String
            Public Property VLogTypeB() As String
                Get
                    Return Me.VLogTypeBField
                End Get
                Set(ByVal value As String)
                    Me.VLogTypeBField = value
                End Set
            End Property

            Public VLogTypeCField As String
            Public Property VLogTypeC() As String
                Get
                    Return Me.VLogTypeCField
                End Get
                Set(ByVal value As String)
                    Me.VLogTypeCField = value
                End Set
            End Property

        End Class

        Private Function GetMyDT() As Data.DataTable
            Dim MyDT As Data.DataTable
            Dim sql As New MyStringBuilder
            Dim dat As New clsData
            If data.ddlTownID.SelectedValue <> "" Then

                sql.Length = 0
                sql.AppendFormat("Select r2.RegionName+r1.RegionName as TownName ,r1.RegionID from Region r1 ")
                sql.AppendFormat("inner join Region r2 on r1.Parent=r2.RegionID ")
                sql.AppendFormat("where r1.RegionType='V'  ")
                sql.AppendFormat("and r2.RegionID='{0}' ", data.ddlTownID.SelectedValue)
                sql.AppendFormat("order by r2.RegionName,r1.RegionName ")

                MyDT = dat.GetData(sql)

            Else
                sql.Length = 0
                sql.AppendFormat("Select RegionName as TownName ,RegionID from Region ")
                sql.AppendFormat("where RegionType='T' ")
                sql.AppendFormat("and RegionID<>'X01' ")
                sql.AppendFormat("order by RegionName ")
                MyDT = dat.GetData(sql)
            End If
            Return MyDT
        End Function

        Private Function GetCount(ByVal MyDT As Data.DataTable, ByVal ColName As String) As Integer
            Dim MyCount As Integer = 0
            For i As Integer = 0 To MyDT.DefaultView.Count - 1
                MyCount += MyDT.DefaultView(i)(ColName)
            Next
            Return MyCount
        End Function
#End Region
    End Class
#Region "CTL-Action Dispatching"
    '依據請求執行 (例如 GridView.Editing)
    Protected Sub btnPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Me.Modeler.DoPrint()
    End Sub
    Private Sub BindDDLTownID()
        Me.ddlTownID.DataTextField = "RegionName"
        Me.ddlTownID.DataValueField = "RegionID"
        Me.ddlTownID.DataSource = clsAuth.GetTownData()
        Me.ddlTownID.DataBind()

        If Me.ddlTownID.Items.Count > 1 Then
            Me.ddlTownID.Items.Insert(0, MyObj.GetDDLSelectAllItem())
        End If

    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Viewer.ReInitData()
        If Not IsPostBack() Then
            Me.BindDDLTownID()
        End If
    End Sub
#End Region
End Class
